USE [Build_File] GO /****** Object: View [dbo].[tbBillExamListing] Script Date: 8/28/2023 2:05:18 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[tbBillExamListing] AS /* IsFreeItem AND chargecount field are added in exams buildfile. PROGRAM WILL SET TO ZERO AMOUNT IF EXAM IS TAGGED AS IsFreeItem */ SELECT 'CT' AS RevenueID, CTExamID AS ItemID, CTExam AS Description, CTSectionId AS SectionID, ISNULL(MedicareType, 'NM') AS MedicareType, ISNULL(MedicareCategory, 'A') AS MedicareCategory, ISNULL(IsSenior, '0') AS Senior, CAST(ISNULL(RateSD, 0) AS Money) AS SeniorDiscount, CAST(ISNULL(RATEA, 0) AS Money) AS RateA, 0 AS WithCommission, CAST(ISNULL(RATEB, 0) AS Money) AS RateB, '' AS GenericID, ISNULL(NM_MedicareType, '') AS NM_MedicareType, ISNULL(FixRate, 'N') AS FixRate, '' as SapCode, '' as AccountCode, CAST(ISNULL(RateD, 0) AS Money) AS RateD, RateDDate, '' as OldItemID, ISNULL(NM_MedicareCategory, '') AS NM_MedicareCategory, CAST(ISNULL(RateH, 0) AS Money) AS RateH, CAST(ISNULL(RateC, 0) AS Money) AS RateC, CAST(ISNULL(RateE, 0) AS Money) AS RateE , CAST(ISNULL(RateF, 0) AS Money) AS RateF, CAST(ISNULL(RateG, 0) AS Money) AS RateG, IsNull(FormType,'') FormType, '' as ReportGroup, '' as Form, OpenQuantity, IsNull(Classification,'0') as Classification, IsNull(ReaderFeeA,0) as ReaderFeeA, IsNull(ReaderFeeB,0) as ReaderFeeB, IsNull(ReaderFeeC,0) as ReaderFeeC, IsNull(ReaderFeeD,0) as ReaderFeeD, IsNull(ReaderFeeE,0) as ReaderFeeE, IsNull(ReaderFeeF,0) as ReaderFeeF, IsNull(ReaderFeeG,0) as ReaderFeeG, IsNull(ReaderFeeH,0) as ReaderFeeH, IsNull(ReaderFeeI,0) as ReaderFeeI, 0 as ProfFeeA, IsNull(ProfFeeB,0) as ProfFeeB, IsNull(ProfFeeC,0) as ProfFeeC, IsNull(ProfFeeD,0) as ProfFeeD, IsNull(ProfFeeE,0) as ProfFeeE , '0' as IsFreeItem, 0 as chargecount,isEmailCharge,CAST(ISNULL(RateI, 0) AS Money) AS RateI, CAST(ISNULL(HMORateA, 0) AS Money) AS HMORateA,CAST(ISNULL(HMORateB, 0) AS Money) AS HMORateB, CAST(ISNULL(HMORateC, 0) AS Money) AS HMORateC,CAST(ISNULL(HMORateD, 0) AS Money) AS HMORateD, CAST(ISNULL(HMORateE, 0) AS Money) AS HMORateE,CAST(ISNULL(HMORateF, 0) AS Money) AS HMORateF, CAST(ISNULL(HMORateG, 0) AS Money) AS HMORateG, CAST(ISNULL(HMOReaderFeeA, 0) AS Money) AS HMOReaderFeeA,CAST(ISNULL(HMOReaderFeeB, 0) AS Money) AS HMOReaderFeeB, CAST(ISNULL(HMOReaderFeeC, 0) AS Money) AS HMOReaderFeeC,CAST(ISNULL(HMOReaderFeeD, 0) AS Money) AS HMOReaderFeeD, CAST(ISNULL(HMOReaderFeeE, 0) AS Money) AS HMOReaderFeeE,CAST(ISNULL(HMOReaderFeeF, 0) AS Money) AS HMOReaderFeeF, CAST(ISNULL(HMOReaderFeeG, 0) AS Money) AS HMOReaderFeeG FROM BUILD_FILE.dbo.TbCoCTExam UNION ALL SELECT 'NU' AS RevenueID, NucExamID AS ItemID, NucExam AS Description, NucSectionID AS SectionID, ISNULL(MedicareType, 'NM') AS MedicareType, ISNULL(MedicareCategory, 'A') AS MedicareCategory, ISNULL(IsSenior, '0') AS Senior, CAST(ISNULL(RateSD, 0) AS Money) AS SeniorDiscount, CAST(ISNULL(RATEA, 0) AS Money) AS RateA, 0 AS WithCommission, CAST(ISNULL(RATEB, 0) AS Money) AS RateB, '' AS GenericID, ISNULL(NM_MedicareType, '') AS NM_MedicareType, ISNULL(FixRate, 'N') AS FixRate, '' as SapCode, '' as AccountCode, CAST(ISNULL(RateD, 0) AS Money) AS RateD, RateDDate, '' as OldItemID, ISNULL(NM_MedicareCategory, '') AS NM_MedicareCategory, CAST(ISNULL(RateH, 0) AS Money) AS RateH, CAST(ISNULL(RateC, 0) AS Money) AS RateC, CAST(ISNULL(RateE, 0) AS Money) AS RateE , CAST(ISNULL(RateF, 0) AS Money) AS RateF, CAST(ISNULL(RateG, 0) AS Money) AS RateG, IsNull(FormType,'') FormType, '' as ReportGroup, '' as Form, OpenQuantity, IsNull(Classification,'0') as Classification, 0 as ReaderFeeA, 0 as ReaderFeeB, 0 as ReaderFeeC, 0 as ReaderFeeD, 0 as ReaderFeeE, 0 as ReaderFeeF, 0 as ReaderFeeG, 0 as ReaderFeeH, 0 as ReaderFeeI, 0 as ProfFeeA, IsNull(ProfFeeB,0) as ProfFeeB, IsNull(ProfFeeC,0) as ProfFeeC, IsNull(ProfFeeD,0) as ProfFeeD, IsNull(ProfFeeE,0) as ProfFeeE , '0' as IsFreeItem, 0 as chargecount,isEmailCharge,CAST(ISNULL(RateI, 0) AS Money) AS RateI, CAST(ISNULL(HMORateA, 0) AS Money) AS HMORateA,CAST(ISNULL(HMORateB, 0) AS Money) AS HMORateB, CAST(ISNULL(HMORateC, 0) AS Money) AS HMORateC,CAST(ISNULL(HMORateD, 0) AS Money) AS HMORateD, CAST(ISNULL(HMORateE, 0) AS Money) AS HMORateE,CAST(ISNULL(HMORateF, 0) AS Money) AS HMORateF, CAST(ISNULL(HMORateG, 0) AS Money) AS HMORateG, CAST(ISNULL(HMOReaderFeeA, 0) AS Money) AS HMOReaderFeeA,CAST(ISNULL(HMOReaderFeeB, 0) AS Money) AS HMOReaderFeeB, CAST(ISNULL(HMOReaderFeeC, 0) AS Money) AS HMOReaderFeeC,CAST(ISNULL(HMOReaderFeeD, 0) AS Money) AS HMOReaderFeeD, CAST(ISNULL(HMOReaderFeeE, 0) AS Money) AS HMOReaderFeeE,CAST(ISNULL(HMOReaderFeeF, 0) AS Money) AS HMOReaderFeeF, CAST(ISNULL(HMOReaderFeeG, 0) AS Money) AS HMOReaderFeeG FROM BUILD_FILE.dbo.tbCoNucExam UNION ALL SELECT 'XR' AS RevenueID, XrayExamID AS ItemID, XrayExam AS Description, XraySectionId AS SectionID, ISNULL(MedicareType, 'NM') AS MedicareType, ISNULL(MedicareCategory, 'A') AS MedicareCategory, ISNULL(IsSenior, '0') AS Senior, CAST(ISNULL(RateSD, 0) AS Money) AS SeniorDiscount, CAST(ISNULL(RateA, 0) AS Money) AS RateA, ISNULL(WithCommission, 0) AS WithCommission, CAST(ISNULL(RateB, 0) AS Money) AS RateB, '' AS GenericID, ISNULL(NM_MedicareType, '') AS NM_MedicareType, ISNULL(FixRate, 'N') AS FixRate, '' as SapCode, '' as AccountCode, CAST(ISNULL(RateD, 0) AS Money) AS RateD, RateDDate, '' OldItemID, ISNULL(NM_MedicareCategory, '') AS NM_MedicareCategory, CAST(ISNULL(RateH, 0) AS Money) AS RateH, CAST(ISNULL(RateC, 0) AS Money) AS RateC, CAST(ISNULL(RateE, 0) AS Money) AS RateE , CAST(ISNULL(RateF, 0) AS Money) AS RateF, CAST(ISNULL(RateG, 0) AS Money) AS RateG, IsNull(FormType,'') FormType, '' as ReportGroup, '' as Form, OpenQuantity, IsNull(Classification,'0') as Classification, IsNull(ReaderFeeA,0) as ReaderFeeA, IsNull(ReaderFeeB,0) as ReaderFeeB, IsNull(ReaderFeeC,0) as ReaderFeeC, IsNull(ReaderFeeD,0) as ReaderFeeD, IsNull(ReaderFeeE,0) as ReaderFeeE, IsNull(ReaderFeeF,0) as ReaderFeeF, IsNull(ReaderFeeG,0) as ReaderFeeG, IsNull(ReaderFeeH,0) as ReaderFeeH, IsNull(ReaderFeeI,0) as ReaderFeeI, IsNull(ProfFeeA,0) as ProfFeeA, IsNull(ProfFeeB,0) as ProfFeeB, IsNull(ProfFeeC,0) as ProfFeeC, IsNull(ProfFeeD,0) as ProfFeeD, IsNull(ProfFeeE,0) as ProfFeeE , '0' as IsFreeItem, 0 as chargecount,isEmailCharge,CAST(ISNULL(RateI, 0) AS Money) AS RateI, CAST(ISNULL(HMORateA, 0) AS Money) AS HMORateA,CAST(ISNULL(HMORateB, 0) AS Money) AS HMORateB, CAST(ISNULL(HMORateC, 0) AS Money) AS HMORateC,CAST(ISNULL(HMORateD, 0) AS Money) AS HMORateD, CAST(ISNULL(HMORateE, 0) AS Money) AS HMORateE,CAST(ISNULL(HMORateF, 0) AS Money) AS HMORateF, CAST(ISNULL(HMORateG, 0) AS Money) AS HMORateG, CAST(ISNULL(HMOReaderFeeA, 0) AS Money) AS HMOReaderFeeA,CAST(ISNULL(HMOReaderFeeB, 0) AS Money) AS HMOReaderFeeB, CAST(ISNULL(HMOReaderFeeC, 0) AS Money) AS HMOReaderFeeC,CAST(ISNULL(HMOReaderFeeD, 0) AS Money) AS HMOReaderFeeD, CAST(ISNULL(HMOReaderFeeE, 0) AS Money) AS HMOReaderFeeE,CAST(ISNULL(HMOReaderFeeF, 0) AS Money) AS HMOReaderFeeF, CAST(ISNULL(HMOReaderFeeG, 0) AS Money) AS HMOReaderFeeG FROM BUILD_FILE.dbo.TbCoXrayExam UNION ALL SELECT 'US' AS RevenueID, UltraExamID AS ItemID, UltraExam AS Description, UltraSectionId AS SectionID, ISNULL(MedicareType, 'NM') AS MedicareType, ISNULL(MedicareCategory, 'A') AS MedicareCategory, ISNULL(IsSenior, '0') AS Senior, CAST(ISNULL(RateSD, 0) AS Money) AS SeniorDiscount, CAST(ISNULL(RateA, 0) AS Money) AS RateA, 0 AS WithCommission, CAST(ISNULL(RateB, 0) AS Money) AS RateB, '' AS GenericID, ISNULL(NM_MedicareType, '') AS NM_MedicareType, ISNULL(FixRate, 'N') AS FixRate, '' as SapCode, '' as AccountCode, CAST(ISNULL(RateD, 0) AS Money) AS RateD, RateDDate, '' OldItemID, ISNULL(NM_MedicareCategory, '') AS NM_MedicareCategory, CAST(ISNULL(RateH, 0) AS Money) AS RateH, CAST(ISNULL(RateC, 0) AS Money) AS RateC, CAST(ISNULL(RateE, 0) AS Money) AS RateE , CAST(ISNULL(RateF, 0) AS Money) AS RateF, CAST(ISNULL(RateG, 0) AS Money) AS RateG, IsNull(FormType,'') FormType, '' as ReportGroup, '' as Form, OpenQuantity, IsNull(Classification,'0') as Classification, IsNull(ReaderFeeA,0) as ReaderFeeA, IsNull(ReaderFeeB,0) as ReaderFeeB, IsNull(ReaderFeeC,0) as ReaderFeeC, IsNull(ReaderFeeD,0) as ReaderFeeD, IsNull(ReaderFeeE,0) as ReaderFeeE, IsNull(ReaderFeeF,0) as ReaderFeeF, IsNull(ReaderFeeG,0) as ReaderFeeG, IsNull(ReaderFeeH,0) as ReaderFeeH, IsNull(ReaderFeeI,0) as ReaderFeeI, IsNull(ProfFeeA,0) as ProfFeeA, IsNull(ProfFeeB,0) as ProfFeeB, IsNull(ProfFeeC,0) as ProfFeeC, IsNull(ProfFeeD,0) as ProfFeeD, IsNull(ProfFeeE,0) as ProfFeeE ,0 as IsFreeItem, 0 as chargecount,isEmailCharge,CAST(ISNULL(RateI, 0) AS Money) AS RateI, CAST(ISNULL(HMORateA, 0) AS Money) AS HMORateA,CAST(ISNULL(HMORateB, 0) AS Money) AS HMORateB, CAST(ISNULL(HMORateC, 0) AS Money) AS HMORateC,CAST(ISNULL(HMORateD, 0) AS Money) AS HMORateD, CAST(ISNULL(HMORateE, 0) AS Money) AS HMORateE,CAST(ISNULL(HMORateF, 0) AS Money) AS HMORateF, CAST(ISNULL(HMORateG, 0) AS Money) AS HMORateG, CAST(ISNULL(HMOReaderFeeA, 0) AS Money) AS HMOReaderFeeA,CAST(ISNULL(HMOReaderFeeB, 0) AS Money) AS HMOReaderFeeB, CAST(ISNULL(HMOReaderFeeC, 0) AS Money) AS HMOReaderFeeC,CAST(ISNULL(HMOReaderFeeD, 0) AS Money) AS HMOReaderFeeD, CAST(ISNULL(HMOReaderFeeE, 0) AS Money) AS HMOReaderFeeE,CAST(ISNULL(HMOReaderFeeF, 0) AS Money) AS HMOReaderFeeF, CAST(ISNULL(HMOReaderFeeG, 0) AS Money) AS HMOReaderFeeG FROM BUILD_FILE.dbo.TbCoUltraExam --UNION ALL --SELECT 'RI' AS RevenueID, MRIExamID AS ItemID, MRIExam AS Description, MRISectionID AS SectionID, ISNULL(MedicareType, 'NM') AS MedicareType, -- ISNULL(MedicareCategory, 'A') AS MedicareCategory, '0' AS Senior, '0' AS SeniorDiscount, -- CAST(ISNULL(RateA, 0) AS Money) AS RateA, 0 AS WithCommission, CAST(ISNULL(RateB, 0) AS Money) AS RateB, '' AS GenericID, -- ISNULL(NM_MedicareType, '') AS NM_MedicareType, ISNULL(FixRate, 'N') AS FixRate, '' as SapCode, '' as AccountCode, -- CAST(ISNULL(RateD, 0) AS Money) AS RateD, '0' as RateDDate, '' OldItemID, ISNULL(NM_MedicareCategory, '') AS NM_MedicareCategory, -- '0' AS RateH, CAST(ISNULL(RateC, 0) AS Money) AS RateC, CAST(ISNULL(RateE, 0) AS Money) AS RateE , -- '0' AS RateF, '0' AS RateG, '4' FormType, -- '' as ReportGroup, '' as Form, '0' as OpenQuantity, IsNull(Classification,'0') as Classification, -- IsNull(ReaderFeeA,0) as ReaderFeeA, IsNull(ReaderFeeB,0) as ReaderFeeB, IsNull(ReaderFeeC,0) as ReaderFeeC, IsNull(ReaderFeeD,0) as ReaderFeeD, -- IsNull(ReaderFeeE,0) as ReaderFeeE, IsNull(ProfFeeA,0) as ProfFeeA, IsNull(ProfFeeB,0) as ProfFeeB, IsNull(ProfFeeC,0) as ProfFeeC, -- IsNull(ProfFeeD,0) as ProfFeeD, IsNull(ProfFeeE,0) as ProfFeeE --FROM BUILD_FILE.dbo.tbCoMRIExam UNION ALL SELECT OtherSectionID AS RevenueID, OtherRevenueID AS ItemID, OtherRevenue AS Description, SectionID, ISNULL(MedicareType, 'NM') AS MedicareType, ISNULL(MedicareCategory, 'A') AS MedicareCategory, ISNULL(IsSenior, '0') AS Senior, CAST(ISNULL(RateSD, 0) AS Money) AS SeniorDiscount, CAST(ISNULL(RateA, 0) AS Money) AS RateA, ISNULL(WithCommission, 0) AS WithCommission, CAST(ISNULL(RateB, 0) AS Money) AS RateB, ISNULL(GenericID, '') AS GenericID, ISNULL(NM_MedicareType, '') AS NM_MedicareType, ISNULL(FixRate, 'N') AS FixRate, isnull(SapCode,'') as SapCode, isnull(AccountCode,'') as AccountCode, CAST(ISNULL(RateD, 0) AS Money) AS RateD, RateDDate, isnull(PreviousID,'') as OldItemID, ISNULL(NM_MedicareCategory, '') AS NM_MedicareCategory, CAST(ISNULL(RateH, 0) AS Money) AS RateH, CAST(ISNULL(RateC, 0) AS Money) AS RateC, CAST(ISNULL(RateE, 0) AS Money) AS RateE , CAST(ISNULL(RateF, 0) AS Money) AS RateF, CAST(ISNULL(RateG, 0) AS Money) AS RateG, IsNull(FormType,'') FormType, '' as ReportGroup, '' as Form, OpenQuantity, '0' as Classification, IsNull(ReaderFeeA,0) as ReaderFeeA, IsNull(ReaderFeeB,0) as ReaderFeeB, IsNull(ReaderFeeC,0) as ReaderFeeC, IsNull(ReaderFeeD,0) as ReaderFeeD, IsNull(ReaderFeeE,0) as ReaderFeeE, IsNull(ReaderFeeF,0) as ReaderFeeF, IsNull(ReaderFeeG,0) as ReaderFeeG, IsNull(ReaderFeeH,0) as ReaderFeeH, IsNull(ReaderFeeI,0) as ReaderFeeI, IsNull(ProfFeeA,0) as ProfFeeA, IsNull(ProfFeeB,0) as ProfFeeB, IsNull(ProfFeeC,0) as ProfFeeC, IsNull(ProfFeeD,0) as ProfFeeD, IsNull(ProfFeeE,0) as ProfFeeE , '0' as IsFreeItem, 0 as chargecount,isEmailCharge,CAST(ISNULL(RateI, 0) AS Money) AS RateI, CAST(ISNULL(HMORateA, 0) AS Money) AS HMORateA,CAST(ISNULL(HMORateB, 0) AS Money) AS HMORateB, CAST(ISNULL(HMORateC, 0) AS Money) AS HMORateC,CAST(ISNULL(HMORateD, 0) AS Money) AS HMORateD, CAST(ISNULL(HMORateE, 0) AS Money) AS HMORateE,CAST(ISNULL(HMORateF, 0) AS Money) AS HMORateF, CAST(ISNULL(HMORateG, 0) AS Money) AS HMORateG, CAST(ISNULL(HMOReaderFeeA, 0) AS Money) AS HMOReaderFeeA,CAST(ISNULL(HMOReaderFeeB, 0) AS Money) AS HMOReaderFeeB, CAST(ISNULL(HMOReaderFeeC, 0) AS Money) AS HMOReaderFeeC,CAST(ISNULL(HMOReaderFeeD, 0) AS Money) AS HMOReaderFeeD, CAST(ISNULL(HMOReaderFeeE, 0) AS Money) AS HMOReaderFeeE,CAST(ISNULL(HMOReaderFeeF, 0) AS Money) AS HMOReaderFeeF, CAST(ISNULL(HMOReaderFeeG, 0) AS Money) AS HMOReaderFeeG FROM BUILD_FILE.dbo.TbCoOtherRevenue WHERE (OtherSectionID <> 'AU') AND (OtherSectionID <> 'NU') AND (OtherSectionID <> 'XR') AND (OtherSectionID <> 'HS') AND (OtherSectionID <> 'US') AND (OtherSectionID <> 'LB') AND (OtherSectionID <> 'PA') AND (OtherSectionID <> 'CT') AND (OtherSectionID <> 'PT') AND (OtherSectionID <> 'HI') AND (OtherSectionID <> 'BA') AND (OtherSectionID <> 'BD') GO